"""Optimize product models for 3NF compliance

Revision ID: 20241005_optimize_v3
Revises: 1eda163dc581
Create Date: 2025-10-05 18:35:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision: str = '20241005_optimize_v3'
down_revision: Union[str, Sequence[str], None] = '1eda163dc581'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema - Phase 1: Create new tables"""
    # ### commands auto generated by Alembic - please adjust! ###

    # 1. Create attribute definitions table
    op.create_table('attribute_definitions',
        sa.Column('id', sa.Integer(), nullable=False, comment='属性定义ID'),
        sa.Column('name', sa.String(length=100), nullable=False, comment='属性名称'),
        sa.Column('code', sa.String(length=50), nullable=False, comment='属性代码（如color, size, capacity）'),
        sa.Column('description', sa.Text(), nullable=True, comment='属性描述'),
        sa.Column('input_type', sa.String(length=20), server_default='select', nullable=True, comment='输入类型：select, text, number等'),
        sa.Column('is_required', sa.Boolean(), nullable=False, default=False, comment='是否必填'),
        sa.Column('is_filterable', sa.Boolean(), nullable=False, default=True, comment='是否可用于筛选'),
        sa.Column('sort_order', sa.Integer(), nullable=False, default=0, comment='排序顺序'),
        sa.Column('is_active', sa.Boolean(), nullable=False, default=True, comment='是否激活'),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('code'),
        sa.UniqueConstraint('name')
    )
    op.create_index('ix_attribute_definitions_id', 'attribute_definitions', ['id'], unique=False)

    # 2. Create attribute values table
    op.create_table('attribute_values',
        sa.Column('id', sa.Integer(), nullable=False, comment='属性值ID'),
        sa.Column('attribute_id', sa.Integer(), nullable=False, comment='属性定义ID'),
        sa.Column('value', sa.String(length=100), nullable=False, comment='属性值'),
        sa.Column('display_name', sa.String(length=100), nullable=False, comment='显示名称'),
        sa.Column('price_adjustment', sa.Integer(), nullable=False, default=0, comment='价格调整（分为单位）'),
        sa.Column('sort_order', sa.Integer(), nullable=False, default=0, comment='排序顺序'),
        sa.Column('is_active', sa.Boolean(), nullable=False, default=True, comment='是否激活'),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_attribute_values_id', 'attribute_values', ['id'], unique=False)

    # 3. Create specification groups table
    op.create_table('specification_groups',
        sa.Column('id', sa.Integer(), nullable=False, comment='规格分组ID'),
        sa.Column('name', sa.String(length=100), nullable=False, comment='分组名称'),
        sa.Column('description', sa.Text(), nullable=True, comment='分组描述'),
        sa.Column('sort_order', sa.Integer(), nullable=False, default=0, comment='排序顺序'),
        sa.Column('is_active', sa.Boolean(), nullable=False, default=True, comment='是否激活'),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('name')
    )
    op.create_index('ix_specification_groups_id', 'specification_groups', ['id'], unique=False)

    # 4. Create specification definitions table
    op.create_table('specification_definitions',
        sa.Column('id', sa.Integer(), nullable=False, comment='规格定义ID'),
        sa.Column('group_id', sa.Integer(), nullable=True, comment='规格分组ID'),
        sa.Column('name', sa.String(length=100), nullable=False, comment='规格名称'),
        sa.Column('code', sa.String(length=50), nullable=False, comment='规格代码'),
        sa.Column('data_type', sa.String(length=20), nullable=False, default='text', comment='数据类型：text, number, boolean等'),
        sa.Column('unit', sa.String(length=20), nullable=True, comment='单位（如英寸、GB等）'),
        sa.Column('description', sa.Text(), nullable=True, comment='规格描述'),
        sa.Column('sort_order', sa.Integer(), nullable=False, default=0, comment='排序顺序'),
        sa.Column('is_active', sa.Boolean(), nullable=False, default=True, comment='是否激活'),
        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='创建时间'),
        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True, comment='更新时间'),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('code'),
        sa.UniqueConstraint('name')
    )
    op.create_index('ix_specification_definitions_id', 'specification_definitions', ['id'], unique=False)

    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema"""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('ix_specification_definitions_id', table_name='specification_definitions')
    op.drop_table('specification_definitions')
    op.drop_index('ix_specification_groups_id', table_name='specification_groups')
    op.drop_table('specification_groups')
    op.drop_index('ix_attribute_values_id', table_name='attribute_values')
    op.drop_table('attribute_values')
    op.drop_index('ix_attribute_definitions_id', table_name='attribute_definitions')
    op.drop_table('attribute_definitions')
    # ### end Alembic commands ###